package hn.cch.framework.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import hn.cch.framework.util.ExcelUtil;
import org.junit.Assert;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.StandardOpenOption;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class EasyExcelTest {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelTest.class);


    public String excel_path = "src/test/resources/excel";

    @Test
    public void testExcel1() {

        Map<Integer, Object> rowData = new HashMap<>();
        rowData.put(1, true);
        rowData.put(2, 1.1);
        rowData.put(3, "1-=1");
        rowData.put(4, BigDecimal.valueOf(10.01));
        Map<Integer, Map<Integer, Object>> sheetData = new HashMap<>();
        sheetData.put(5, rowData);
        Map<String, Map<Integer, Map<Integer, Object>>> excelData = new HashMap<>();
        excelData.put("0", sheetData);

        File excelFile = new File("src/test/resources/excel/excel.xlsx");
        Assert.assertTrue(excelFile.exists());
        String excelPath = excelFile.getAbsolutePath();
        String writePath = String.format("%s-%s.xlsx", excelPath.substring(0, excelPath.length() - 5), 1);
        logger.info("writePath:{}", writePath);
        ExcelUtil.save(excelFile, writePath, excelData);


    }


    @Test
    public void testExcel2() {

        Map<String, Object> headerMap = new HashMap<>();
        headerMap.put("table_title", "标题");
        headerMap.put("description", "描述");
        headerMap.put("report_date", "2021-09-29");

        headerMap.put("unit", "元");
        headerMap.put("format", "yyyy-MM-dd HH:mm:ss");
        List<ExcelModel> excelData = new ArrayList<>();

        excelData.add(new ExcelModel("hn", "2021-09-09 19:39:50", 12.3));
        excelData.add(new ExcelModel("cch", "2021-09-19 19:29:39", 45.6));
        excelData.add(new ExcelModel("chench", "2021-09-29 39:49:59", 78.9));

        File excelFile = new File("src/test/resources/excel/templates/template.xlsx");
        Assert.assertTrue(excelFile.exists());
        String excelPath = excelFile.getAbsolutePath();
        String writePath = String.format("%s-%s.xlsx", excelPath.substring(0, excelPath.length() - 5), System.nanoTime());
        logger.info("writePath:{}", writePath);
        String sheetName = "sheet";
        ExcelUtil.save(excelFile, writePath, sheetName, headerMap, excelData);


    }


    @Test
    public void testExcel3() {


        String sheetName = "sheet";
        List<ExcelModel> excelData = new ArrayList<>();

        excelData.add(new ExcelModel("hn", "2021-09-09 19:39:50", 12.3));
        excelData.add(new ExcelModel("cch", "2021-09-19 19:29:39", 45.6));
        excelData.add(new ExcelModel("chench", "2021-09-29 39:49:59", 78.9));

        // 横向模板
        File excelFile = new File("src/test/resources/excel/templates/horizontal.xlsx");
        Assert.assertTrue(excelFile.exists());
        String excelPath = excelFile.getAbsolutePath();
        String writePath = String.format("%s-%s.xlsx", excelPath.substring(0, excelPath.length() - 5), System.nanoTime());
        logger.info("writePath:{}", writePath);

        ExcelUtil.save(excelFile, writePath, sheetName, false, excelData);


    }


    @Test
    public void testExcel4() {

        try {
            List<ExcelModel> excelData = new ArrayList<>();
            excelData.add(new ExcelModel("hn", "2021-09-09 19:39:50", 12.3));
            excelData.add(new ExcelModel("cch", "2021-09-19 19:29:39", 45.6));
            excelData.add(new ExcelModel("chench", "2021-09-29 39:49:59", 78.9));

            List<List<String>> excelList = new ArrayList<>();
            for (int i = 0; i < 3; i++) {
                List<String> list = new ArrayList<>();
                list.add(String.format("第%d个", i + 1));
                for (int j = 0; j < 3; j++) {
                    list.add(String.format("%d-%d", i + 1, j));
                }
                excelList.add(list);
            }

            File excelFile = new File("src/test/resources/excel/templates/test.xlsx");
            Assert.assertTrue(excelFile.exists());
            String excelPath = excelFile.getAbsolutePath();
            String writePath = String.format("%s-%s.xlsx", excelPath.substring(0, excelPath.length() - 5), System.nanoTime());
            logger.info("writePath:{}", writePath);

            String sheetName = "sheet";
            ExcelWriter excelWriter = EasyExcel.write(Files.newOutputStream(new File(writePath).toPath(), StandardOpenOption.APPEND)).withTemplate(Files.newInputStream(excelFile.toPath())).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            // 横向填充
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();

            excelWriter.fill(excelData, fillConfig, writeSheet);// 填充

            excelWriter.write(excelList, writeSheet); // 写入

            excelWriter.finish();
        } catch (Exception e) {
            logger.error(e.getMessage());

        }

    }


    @Test
    public void testExcel5() {

        try {
            List<String> excelHead0 = new ArrayList<>();
            excelHead0.add("名称");
            // excelHead0.add("{.name}");
            List<String> excelHead1 = new ArrayList<>();
            excelHead1.add("时间");
            // excelHead1.add("{.time}");
            List<String> excelHead2 = new ArrayList<>();
            excelHead2.add("数据");
            // excelHead2.add("{.info}");
            List<List<String>> excelHead = new ArrayList<>();
            excelHead.add(excelHead0);
            excelHead.add(excelHead1);
            excelHead.add(excelHead2);

            List<String> templates = new ArrayList<>();
            // 给模版指定前缀作为标识区分不同模版
            templates.add("{data1.name}");
            templates.add("{data1.time}");
            templates.add("{data1.info}");
            templates.add("{data2.name}");
            templates.add("{data2.time}");
            templates.add("{data2.info}");

            List<List<String>> excelInfo = new ArrayList<>();
            excelInfo.add(templates);

            String sheetName = "sheet";
            File excelFile = new File("src/test/resources/excel/templates/custom.xlsx");
            EasyExcel.write(excelFile)
                    // .useDefaultStyle(false)
                    // .inMemory(true) 由于解决生成模版后填充数据时无法替换的问题
                    .inMemory(true).head(excelHead).sheet(sheetName).doWrite(excelInfo);


            List<ExcelModel> excelData = new ArrayList<>();
            excelData.add(new ExcelModel("hn", "2021-09-09 19:39:50", 12.3));
            excelData.add(new ExcelModel("cch", "2021-09-19 19:29:39", 45.6));
            excelData.add(new ExcelModel("chench", "2021-09-29 39:49:59", 78.9));

            String excelPath = excelFile.getAbsolutePath();
            String writePath = String.format("%s-%s.xlsx", excelPath.substring(0, excelPath.length() - 5), System.nanoTime());
            logger.info("writePath:{}", writePath);

            ExcelWriter excelWriter = EasyExcel.write(Files.newOutputStream(new File(writePath).toPath(), StandardOpenOption.APPEND))
                    .withTemplate(Files.newInputStream(excelFile.toPath())).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(sheetName).build();
            // 按模版不同标示分开进行多次填充
            excelWriter.fill(new FillWrapper("data1", excelData), writeSheet1);// 填充
            excelWriter.fill(new FillWrapper("data2", excelData), writeSheet1);// 填充
            excelWriter.finish();
        } catch (Exception e) {
            logger.error(e.getMessage());

        }
    }


    @Test
    public void testExcel6() {
        try {
            Map<String, Object> headerMap = new HashMap<>();
            headerMap.put("table_title", "标题");
            headerMap.put("description", "描述");
            headerMap.put("report_date", "2021-09-29");

            headerMap.put("unit", "元");
            headerMap.put("format", "yyyy-MM-dd HH:mm:ss");
            List<ExcelModel> excelData = new ArrayList<>();

            excelData.add(new ExcelModel("hn", "2021-09-09 19:39:50", 12.3));
            excelData.add(new ExcelModel("hang", "2021-09-09 19:39:50", 12.3));
            excelData.add(new ExcelModel("cch", "2021-09-19 19:29:39", 45.6));
            excelData.add(new ExcelModel("cch", "2021-09-29 39:49:59", 45.6));
            excelData.add(new ExcelModel("chench", "2021-09-29 39:49:59", 78.9));

            File excelFile = new File("src/test/resources/excel/templates/template.xlsx");
            Assert.assertTrue(excelFile.exists());
            String excelPath = excelFile.getAbsolutePath();
            String writePath = String.format("%s-%s.xlsx", excelPath.substring(0, excelPath.length() - 5), System.nanoTime());
            logger.info("writePath:{}", writePath);
            String sheetName = "sheet";

            // 合并单元格
            LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);


            ExcelWriter excelWriter = EasyExcel.write(Files.newOutputStream(new File(writePath).toPath(), StandardOpenOption.APPEND)).withTemplate(Files.newInputStream(excelFile.toPath())).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(sheetName).build();

            ExcelUtil.save(excelFile, writePath, sheetName, headerMap, excelData);
        } catch (Exception e) {
            logger.error(e.getMessage());

        }

    }
}
